PL/SQL :: Tuning Greater Than Predicate Query On Dates
Aug 2, 2012
I have the below query which is doing FTS and is very expensive causing load to timeout.
I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.
I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.
The following sequence of simple statements shows me how Analytics and Predicate Pushing appear to work. But when I go from constant tests to a join with the same data as a row instead of a constant value, the Predicate Pushing stops.
I have a table with 9 million rows in it. It takes about 90 seconds to scan. 13:25:11 SQL> 13:25:11 SQL> select count(*) thecount from lv_pln_usge_fact ; THECOUNT ---------- 8681388
1 row selected.
Elapsed: 00:00:32.28
If I run an analytic that counts all rows in the table I can see that easy enough. This exampe as I understand it, scans the table (I know this because if for no other reason, it take 90 seconds to get an answer), then after scanning all rows, counts them and adds the count to each row. There are in fact 9500 or so rows with the values of lv_rqst shown. Instead of seeing all 9500 rows, I distinct it to get only one. See how the count shows all rows in the table.
From this we see that the predicate is not pushed into the inner query to filter rows. If it had, the analytic would have produced a number like 9500 not 9 million. I have no issue with this as this is how Analytics are documented work.
13:25:43 SQL> select distinct * 13:25:43 2 from ( 13:25:43 3 select LV_RQST 13:25:43 4 ,count(*) over () thecount
[code]..
We can see how filter does happen with an analytic. We push the predicate into the inner query and all of a sudden we get a count of only those rows for the specific request. Thus we see the basics of how analytics work, particularly with respect to predicate pushing. There is not real rocket science here. The filter removed rows before the analytic counted them. This too is how Analytics are documented to work.
13:27:02 SQL> select distinct * 13:27:02 2 from ( 13:27:02 3 select lv_rqst,count(*) over () thecount 13:27:02 4 from lv_pln_usge_fact 13:27:02 5 where lv_rqst = '746780192'
[code]...
So far we have been doing "all rows" analytics. Now we use a PARTITION clause to group the data. Notice the rowcount. It is the count for just the lv_rqst. Do not be fooled. This is because the PARTITION column says to synchronize the analytic count to the data for its associated row. Thus the counts will be grouped by LV_RQST. Whether we had the predicate on the outside of the query or not, for this specific lv_rqst shown we would still get this count. This is not proof that we did any filtering with the predicate lv_rqst = '746780192'.
However, notice the speed of the query. It gets done so fast, that there is no way it is scanning the table. I know there is an index that starts with LV_RQST so I conclude that the index was used which I believe means the predicate was in fact PUSHED into the inner query. Thus I think we did in fact filter the rows to just this single LV_RQST value and we also accessed the table using the predicate as well which became an index range scan instead of full table scan. My biggest point is we were able to use the index to get the data we want, not scan the table and this was made so because we added the LV_RQST column to the OVER clause as part of the PARTITION BY expression.
13:27:03 SQL> select distinct * 13:27:03 2 from ( 13:27:03 3 select LV_RQST 13:27:03 4 ,count(*) over (partition by lv_rqst) thecount
[code]...
Now I add an additional column to the inner query, and an additional predicate against that column to the outer query. We know that 9539 is the count of all rows where LV_RQST='746780192' as we have seen that before above. I also know the data and know about half say Y and half say N for this indicator column. This query gives the right answer. I just put it here are additional demonstration of the way analytics work. It demonstrates that the new predicate is not pushed into the innser query to filter rows. Again this is how Analytics are documented to work.
It used the index to range scan only the rows where LV_RQST='746780192'. So only those predicates that have their columns in the anlytics OVER clause are allowed to be pushed into the query for filtering and accessing purposes.
13:27:03 SQL> col LV_PLN_USGE_DEL_IND format a20 trunc 13:27:03 SQL> select distinct * 13:27:03 2 from ( 13:27:03 3 select LV_RQST
[code]...
This next query shows a little more clarity. If we add the indicator column to our OVER clause then the rowcount changes to be the number of rows where LV_RQST='746780192' AND lv_pln_usge_del_ind = 'N'. So by putting the column into the OVER expression, Oracle decides to push the predicate down into the inner query and filter the data before the analytic count is done. Again this just demonstrates for clarity how it works. I think I am describing it right anyway.
And once again the speed clearly indicates that an index range scan was done using LV_RQST='746780192'. Recall I said there is an index that starts with this column.
NOW WE COME TO MY PROBLEM.Instead of using the constant value '746780192' we are going to create a one column one row table that has this value in it. We are then going to join to the the analytic subquery instead of doing a contant test against it.
Commit complete. Elapsed: 00:00:00.00 This query is in my mind the same query we did before but we loose the use of the index and go back to doing a FULL TABLE SCAN. 13:27:03 SQL> select distinct x.* 13:27:03 2 from ( 13:27:03 3 select LV_RQST
[code]...
For a little bit more clarity, two more queries. Pay attention to how long it takes, and to how the additional joins affect things. Notice, particularly with the last statement, that the join criteria is being pushed into the inner query with the analytics. Otherwise how did it get that count?
So after looking at all this, here is my question: How do I get Oracle the use the index and nested loop join to the table lv_pln_usge_fact. We know Oracle pushes the predicates down when the columns are referenced in the OVER expression because we see that in several places. We also know the CBO can do a nested loop join with index access on LV_RQST because it does it when we use a constant test. But it won't use the index and nested loop when we do a join to a table with the same data, no matter how much rewriting or hinting I do.
I tested this in 9i/10g/11g and got same behavior in all three places.
I have a weird optimizer behaviour on a 10.2.0.4 db.When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why. Here are the statements:
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
I am sure I read somewhere that it is possible in 11g2 to store Predicate Fragments that work in a similar way to Functions for use in SQL queries?
I think the idea is that instead of defining a function that will form part of a predicate restriction you can define the SQL and then just reference it in your existing code.
For example, lets say I have 10 queries of varying types that only act the subset of ACTIVE customers. An 'ACTIVE' customer is one who has an order in the last 6 months OR receives a catalogue. In terms of SQL this is represented by a couple of table joins and and few 'AND' clauses.
In order to determine the active customers I can use a function in the predicate section of my 10 queries, but I don't want to do this for various reasons (context switching,tuning, etc).
Neither do I want to have to change the PROCEDURE declarations for the procs that store my 10 queries in order to pass in the portion of the SQL that would restrict the data set to ACTIVE customers.
I could just add the required SQL to each of the queries but this means if the definition of ACTIVE alters I have 10 changes to make. Is there a way that that predicate lines that select for 'ACTIVE' can somehow be declared and then this declaration referenced in my SQL queries?
is there a way to return the next 7 dates just using a query... for example, I need a query that returns:
select (I don't know that put here) from dual
Date 2012-10-05 2012-10-06 2012-10-07 2012-10-08 2012-10-09 2012-10-10 2012-10-11
If possible, I would like to know if there's a way to pass a date and based on it, the query returns the next 7 dates based on the passed date... for example:
select (I don't know that put here) from dual where date > '2012-10-15'
Date 2012-10-16 2012-10-17 2012-10-18 2012-10-19 2012-10-20 2012-10-21 2012-10-22
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT * FROM (SELECT Field1, ROW_NUMBER() OVER (ORDER BY field1 desc ) R FROM table WHERE date >= TO_DATE ('20110215', 'YYYYMMDD') AND date < TO_DATE ('20110216', 'YYYYMMDD') ) WHERE R = 1227 --Note: 1227 = (count(field1)/100)-1
MISSING_DATES EMPNO ---------------------- ---------- 09-SEP-12 TO 11-SEP-12 7499 23-SEP-12 TO 26-SEP-12 7499 01-sep-12 TO 30-SEP-12 7521 01-sep-12 TO 30-SEP-12 7788
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
I got a issue with a query to fetch records between two dates for fixed timings
Date From 29-09-2011 to 04-10-2011 Time From 00:00:00hrs to 08:00:00hrs
I tried the below queries, it doesnt work select a.detectorid,sum(b.totalvolume),a.updatetime,a.averagespeed from traffic_data a left outer join volume_data b on a.traffic_id=b.traffic_data_id where pollinterval=1 and detectorid=�AIDC_0154� and updatetime between to_date(�29-aug-2011:00:00:00�,�DD-MON-YYYY:HH24:MI:SS�)
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel ( start_date, end_date ) AS SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE A | 2010 11 01 | 2011 02 11 B | 2011 01 20 | 2011 02 15 C | 2011 03 13 | 2011 04 10 D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]------------------------- B : ------[---]------------------------ C : --------------[---]---------------- D : ----------------[------]-----------
Travel Date : -[--------------------------------]--
BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]-----------
Result X1 : -[-]------------------------------- Result X2 : -----------[--]-------------------- Result X3 : -----------------------------[--]--
I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).
The create table script for the lookup is like this:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59); INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119); etc.
The query is: select r.range_text as duration_range, nvl(count(*),0) as calls, nvl(SUM(call_duration),0) as total_duration from
[code]...
As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.
My questions: 1. Is it possible to get the left join to work with the duration range values as they currently are? 2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.
What I mean is (with only one time value and not lbound and ubound:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0); INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1); INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);
When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value
SQL> drop table precision_test; Table dropped SQL> create table precision_test(name number(2,5)); Table created SQL> insert into precision_test values (1); insert into precision_test values (1) [code]....
I have two sql queries. They run the one after another.
Query 1: select * from capital where member_status = 'MEMBER' AND rownum <= 25 order by price desc
Query 2: select * from capital where member_status = 'MEMBER' AND rownum > 26 order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
Select to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1, to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2, Case when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >
[code]...
from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?
I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT * FROM WORK.WORK_UNIT WHERE (to_char (current_garage) = :entry_blk.curr_garage OR 'all' = :entry_blk.curr_garage) AND route >= 0 AND run >= 0 AND ((package_id > 0 and work_code = 99) OR (package_id is null and work_code = 1)) and Nvl (Emp_No, 0) = 0 AND work_date = :entry_blk.p_work_date AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI') will give me data up to 2 hours
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace PROCEDURE ADD_REWARD_sp( p_proj_id IN NUMBER, p_pledgeAmt IN NUMBER, p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:
SELECT on_time INTO on_time2 FROM work.work_unit WHERE work_code = 1 AND emp_no = :entry_blk.p_emp_no AND work_date = :entry_blk.p_work_date WHERE ROWNUM = 2; --RETURN NULL
I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query
SELECT on_time INTO on_time2 FROM (SELECT on_time FROM work.work_unit WHERE work_code = 1 AND emp_no = :entry_blk.p_emp_no AND work_date = :entry_blk.p_work_date) WHERE ROWNUM = 2;
I can't move forward in my form until I figure out why this is not returning records
I've to create a table which has 650 fields and the total length of CREATE TABLE statement got to be more than 4000 characters.I've to create the table by inserting the CREATE TABLE statment in a variable (V1) then by using EXECUTE IMMEDIATE V1 Since VARCHAR2 only supports upto 4000 characters length string, how can I create such table??
DECLARE V1 VARCHAR2(4000); BEGIN V1 := -- CREATE TALBE STATEMENT WITH LENGTH MORE THAN 4000 EXECUTE IMMEDIATE V1; END;
Quote:got the error -- PL/SQL: numeric or value error: character string buffer too small
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE v_Hire_date date :='12-Dec-2005'; v_five_years BOOLEAN; BEGIN IF [code].....
I have approximately 3 lakh records in mtl_material_transactions table. The value of attribute3 is populated with sysdate based on some business condition.
Out of these 3 lakh records, attribute3 has 5 records which are null. The problem is with the condition 'mmt.attribute3 is null'. The query is pasted below -
select mmt.* from mtl_material_transactions mmt, mtl_transaction_types mtt, mtl_parameters p where mmt.transaction_type_id = mtt.transaction_type_id and mp.organization_id = mmt.organization_id and (upper(mtt.attribute1) ='OUTBOUND' or (upper(mtt.attribute1)='FORM' and upper(mtt.attribute2) in 'ISSUE','RECEIPT')) and 1=decode(mmt.transaction_action_id,2,decode(mmt.subinventory_code,'WIP',0,1),1) and mmt.attribute3 is NULL
