Issue Oracle 18c: I have a query that joins from a view to a subquery (related post: Join between XML queries). with subtype as (select * from sub_lc_events_asset_class_activity_vw), domain as (select cast(rownum as number(38,0)) as rownum_, x.code, x.description, i.name as
Continue readingTag: query-optimization
[SOLVED] Subquery function invoked twice if alias is used in main SQL
Issue I’m trying to understand how Oracle processes SQL’s to study ways of optimizing complex SQL’s. Consider the test function below: CREATE OR REPLACE FUNCTION FCN_SLOW RETURN NUMBER IS BEGIN DBMS_LOCK.SLEEP (5); –5 seconds RETURN 0; END FCN_SLOW; And the
Continue reading[SOLVED] Is the HAVING clause “useless” in a SELECT statement?
Issue I’m reading a paper called Query Optimization Techniques – Tips For Writing Efficient And Faster SQL Queries. That document suggests that the HAVING clause is “useless” in a SELECT statement: Tip #2: Avoid including a HAVING clause in SELECT
Continue reading[SOLVED] Getting records by date is multiples of 30 days
Issue I have the following query to get appointments that need remind once a month if they are not done yet. I want to get records with 30, 60, 90, 120,etc… in the past from the current date. SELECT a.*
Continue reading[SOLVED] Alter distkey for a table with 22 Billions rows never ends
Issue I have a table with 22560627453 rows, with Even diststyle. The sortkey is date I’m trying to run ALTER TABLE movements ALTER DISTSTYLE KEY DISTKEY id; but this never ends. In fact, the cluster is at 75% of disk
Continue reading[SOLVED] How to select multiple items in a subquery SQL Server
Issue I have a query which select multiple items using a subquery as: SELECT DISTINCT A2P.aid, P.pid FROM sub_aminer_author2paper A2P, sub_aminer_paper P WHERE DATALENGTH(P.p_abstract_SWR) > 0 AND P.pid IN (SELECT pid FROM sub_aminer_author2paper WHERE p_year BETWEEN 2005 AND 2014 AND
Continue reading[SOLVED] Composite Index Design (Narrow vs Wide) when searching on date range
Issue I have an argument with my colleague regarding the design of the composite index. If two fields are being used in the query, I reckon the more unique field should be first. Look at this example CREATE TABLE [dbo].[tblPurchase](
Continue reading[SOLVED] I would like to know if there is a better way to write this query (multiple joins of the same table)
Issue here is the problem: I have vehicles table in db (fields of this table are not so important), what’s important is that each vehicle has a model_id, which refers to the vehicle_models table. Vehicle models table has id, class,
Continue reading[SOLVED] Optimize this MS SQL query with a lot of joins
Issue I’m using this query with joins to select specific values from one view: SELECT DISTINCT qir.[Portfolio Company Key] AS portfolio_company_id, qir.[Quarter Date Key] AS quarter_date_id, realized_value.Value AS realized_value, unrealized_value.Value AS unrealized_value, total_fair_value.Value as total_fair_value, multiple.Value as multiple, gross_irr_percentage.Value as
Continue reading[SOLVED] How to optimize my query speed (avoid using subselect for every row)?
Issue I have a table called CisLinkLoadedData. Is has Distributor, Network, Product, DocumentDate, Weight, AmountCP and Quantity columns. It used to store some product daily sales. AmountCP / Quantity is the price for the product at certain date. There are
Continue reading