SQL & PL/SQL :: Predicate Fragment Function
Feb 1, 2012
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?
View 1 Replies
ADVERTISEMENT
Sep 20, 2012
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.
13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST
13:27:03 4 ,LV_PLN_USGE_DEL_IND
[code]...
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.
13:27:03 SQL> create table kevt1
13:27:03 2 (
13:27:03 3 lv_rqst varchar2(10) not null
13:27:03 4 )
13:27:03 5 /
Table created.
Elapsed: 00:00:00.06
13:27:03 SQL>
13:27:03 SQL> insert into kevt1 values ('746780192')
13:27:03 2 /
1 row created.
Elapsed: 00:00:00.00
13:27:03 SQL>
13:27:03 SQL> commit
13:27:03 2 /
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?
14:55:21 SQL> select distinct x.*
14:56:16 2 from (
14:56:16 3 select LV_RQST
14:56:16 4 ,LV_PLN_USGE_DEL_IND
[code]...
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.
View 18 Replies
View Related
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.
below is the query and plan
SELECT TRANSACTION_LOG.ID, TRANSACTION_LOG.USER_IDENTIFIER, TRANSACTION_LOG.START_TIME, TRANSACTION_LOG.END_TIME, TRANSACTION_LOG.REQUEST, TRANSACTION_LOG.RESPONSE ....
View 5 Replies
View Related
May 17, 2012
load data in multiple table using sql loader. I have IN predicate which i don't know is allowed in the sql loader or not
my control file and is as below
LOAD DATA
INFILE 'c: empdemo05.dat'
BADFILE 'c: empad05.bad'
DISCARDFILE 'c: empdisc05.dsc'
REPLACE
[code]....
i am getting below error when executing above error
SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
WHEN DEPTNO IN ('
View 4 Replies
View Related
Apr 29, 2011
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:
A -First statement with suboptimal plan :
SELECT /*KO*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
[code]....
View 1 Replies
View Related
Jun 15, 2012
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
View 5 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
Jun 10, 2010
What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
View 4 Replies
View Related
Apr 4, 2007
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
View 1 Replies
View Related
Mar 24, 2009
I have the following query.
sql
Original
- sql Code
SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
GROUP BY class
ORDER BY class
SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN ( SELECT l_id FROM n_table WHERE id IN (1234)
GROUP BY class
ORDER BY class
It returns two columns: class and the total number of values in that class.
In some cases, there might not be any values under a certain class, so the query won't return anything.
In such a case I want the query to return 0. So what I want to see is:
class:A, COUNT(class):0
I'm trying to use the NVL function here, but either it doesn't work in this context, or it's not the correct syntax the way I'm writing it.
sql
Original
- sql Code
SELECT class, NVL(COUNT (class), 0)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
[Code]....
View 3 Replies
View Related
Dec 16, 2011
I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).
With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.
Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.
My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?
View 12 Replies
View Related
Jul 14, 2010
I've been tasked to parse tags from a string that look like the following:
{Date + XXX}
where XXX represents a numeric value. I have to replace this, including the brace characters with
SYSDATE + XXX
which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following
myString := regexp_replace(myString, '{(Date + [^[::digit:]]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below
myString := regexp_replace(myString, '{(Date + [[:digit:]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...
View 14 Replies
View Related
Sep 11, 2012
I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.
CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)
[code]...
View 4 Replies
View Related
Jun 14, 2011
can i have more than one pl sql blocks in a function and can i use the variable of one cursor into another cursor of the same function?
View 2 Replies
View Related
Oct 20, 2011
There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
View 7 Replies
View Related
Feb 22, 2010
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,
[code]...
View 2 Replies
View Related
Apr 13, 2013
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
View 7 Replies
View Related
Jul 15, 2011
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp
(Sal in number, Commission in number)
return number
as
[code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
View 9 Replies
View Related
Aug 5, 2010
if function having retrun statement, why we need to use out or inout parameters.
View 16 Replies
View Related
Oct 31, 2011
How can i use the decode function?
for example
I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?
View 9 Replies
View Related
Dec 26, 2011
how to use rowtocol means a query.
View 5 Replies
View Related
Apr 4, 2013
DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')
what does it mean..
View 3 Replies
View Related
Mar 22, 2013
Any way to write a function to parse through a clob and extract certain values to insert into a table. I've written the following and it compiles but it doesn't work.
create or replace function all_fields
(type_field VARchar2,
domain_field VARchar2)
return VARchar2 as
typefield VARchar2(100) :=type_field;
domainfield VARchar2(100) :=domain_field;
[Code]....
View 18 Replies
View Related
Aug 30, 2012
CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)
RETURN DATE
IS
TYPE mask_t IS TABLE OF VARCHAR2 (30)INDEX BY BINARY_INTEGER;
fmts mask_t;
[Code]....
When i try using this method i am getting error...
SQL> select my_to_date(to_char(07/11/1987),'yyyy') from dual;
select my_to_date(to_char(07/11/1987),'yyyy') from dual
*
ERROR at line 1:ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_TO_DATE'
what is the error here...What is the solution for this function.....how to execute this function
When i am using ananymous block i am getting this answer:
SQL> declare
2 v date := '07-nov-1987';
3 u date;
4 begin
[Code]...
PL/SQL procedure successfully completed.
View 7 Replies
View Related
Nov 22, 2011
i have one table chequedetails in this 4 rows are there that is
date name sno
1-10-2011 b 1
10-10-2011 c 2
25-10-2011 d 3
11-11-2011 e 4
these records are there in my table in this i selected between date it wil display o/p like this
date name sno
1-10-2011 b 1
11-11-2011 e 2
my query
select * from chequedetails where date between '1-10-2011' and '11-11-2011'
View 22 Replies
View Related
Sep 25, 2013
Can we use commit in a function?
View 10 Replies
View Related
Jul 15, 2012
I am using lag function to display values like below:
order details date starttime
----------------- -------- --------------
main order 1 07/10/12 06:00am
line 1 07/10/12 06:21am
line 2 07/10/12 06:31am
main order 2 07/11/12 07:00am
line 1 07/11/12 07:01am
line 2 07/11/12 07:02am
the data displays correctly when i use lag function except that the line 1 details are never getting displayed ie first line under every order does not get displayed? is using lag function in this case correct?
View 13 Replies
View Related
Jul 9, 2012
explain the flow what exactly is the below function do in detail.
FUNCTION Get_RmtUsr_rec_FUNC (p_MsgType IN VARCHAR2)
RETURN RemoteUser_Rec_Type;FUNCTION Get_RmtUsr_rec_FUNC ( p_MsgType IN VARCHAR2)
RETURN RemoteUser_Rec_Type
IS
[Code]....
View 12 Replies
View Related
Sep 11, 2012
I have written a query which basically retrieves id and created date. IF i put MAX function it is returning id which have max created date. But if i use min function this query is not providing id with min created date,its not returning any rows.
SELECT To_char(OSH.osh_id),
OSH.osh_created
FROM tn_order_status_history osh,
tn_order_status_type ost,
tn_orderline_product op
[code]..........
View 4 Replies
View Related
