| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: realistic cardinality for PL/SQL procedures in SELECT statements
"Gints Plivna" <gints.plivna_at_gmail.com> writes:
>> How can I tell the CBO what number of records the procedure
>> returns?
>
> With cardinality hint.
>
>> So, the query can change its character from a 'pick one record'
>> to a 'filter many records' and the change is driven by the
>> size of the result set of the procedure.
>
> We had the similar problem and it was resolved using 2 query versions
> something like:
> if predicted_count <= treshold then
> SELECT /*+ cardinality(table_name, 1) */ column_list FROM blabla
> else
> SELECT /*+ ALL_ROWS */ column_list FROM blabla
> end if
>
> For the second case you can as well as add some big cardinality number
> if it is necessary.
Hello Gints,
I have tried the CARDINALITY hint and it works if the hinted object is a table but not for functions.
eg:
-- with table TEST_SMALL_RESULTSET
select /*+ cardinality(TEST_SMALL_RESULTSET, 12) */ *
from TEST_SMALL_RESULTSET;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=12 Bytes=468) 1 0 TABLE ACCESS (FULL) OF 'TEST_SMALL_RESULTSET' (Cost=3 Card=12 Bytes=468)
My goggle research showed me some other approaches like
/* FIRST_ROWS DYNAMIC_SAMPLING(TABLE_ALIAS 2) */ to let the optimizer peek into the return values DYNAMIC_SAMPLING seems to switch from CBO to RBO, right? It had no effect in my queries.
or append a WHERE ROWNUM >= 0 to force the conversion to a
temporary table in memory.
It had no effect in my queries, too.
So, none worked.
I have already read T.Kyte's examples on his web site http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3779680732446#15740265481549 Subject "Oracle 9i generates statistics automatically?" comment in reply to Steve's questions from February 18, 2004
and can reproduce his example with STR2TBL as function.
(its in the middle of the topic.)
But, if I try my own procedure in his kind of query
CARDINALITY shows no effect.
I tried many different versions and hints, but failed.
You can find the complete source code below.
> For the first case you can try probably as well
> first_rows_1 hint.
[ use several optimized queries with hints for different
ranges if required ]
I hope to get away with two cases, one for few rows and one for nearly all. But first I have to find two different queries that work.
If you have any comment to my example code below or have an working example please let me know.
Thank you,
Volker
PS.
CARDINALITY does ignore the ',' in the parameter list, right?
CARDINALITY(A 2) and CARDINALITY(A, 2) are OK?
SELECT /*+ cardinality(SG, 10) */ * FROM (
SELECT * from TABLE (
TEST_MODUL.test_data(
CURSOR(SELECT * FROM TEST_SMALL_RESULTSET )
, 5)) SG)
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=8168 Bytes=318552) 1 0 VIEW (Cost=11 Card=8168 Bytes=318552)
2 1 VIEW (Cost=11 Card=8168 Bytes=1078176) 3 2 COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DATA'
SELECT * FROM (
SELECT /*+ cardinality(SG, 200) */ * from TABLE (
TEST_MODUL.test_data(
CURSOR(SELECT * FROM TEST_SMALL_RESULTSET )
, 5)) SG)
SELECT /*+ cardinality(SG, 10) */ * FROM ( SELECT /*+ cardinality(SG, 10) */ * from TABLE ( TEST_MODUL.test_data(
CURSOR(SELECT * FROM TEST_SMALL_RESULTSET )
, 5)) SG)
/* this one mimics the layout of T.Kyte example closer, * but shows no change in the query plan with hints */
select * from TEST_SMALL_RESULTSET
where ID_TABLE_A IN (SELECT /*+ cardinality(SG 3) */ ID_TABLE_A
FROM TABLE (TEST_MODUL.test_data2) SG );
CREATE OR REPLACE PACKAGE TEST_MODUL
IS
FUNCTION test_data( v_cursor IN sys_refcursor
,batch_size IN NUMBER)
RETURN test_data_ids
RETURN test_data_ids
PIPELINED ;
FUNCTION test_data3
RETURN test_data_ids;
END;
/
CREATE OR REPLACE PACKAGE BODY TEST_MODUL
IS
--
FUNCTION test_data( v_cursor IN sys_refcursor
,batch_size IN NUMBER
)
RETURN test_data_ids
PIPELINED AS
ID_TABLE_A coll_number;
ATTRIB_1 coll_number;
ATTRIB_2 coll_number;
--
v_batch_size NUMBER := batch_size;
l_row test_data_rowType;
BEGIN
IF v_batch_size <= 0 THEN
v_batch_size := 100;
END IF;
LOOP
FETCH v_cursor BULK COLLECT
INTO ID_TABLE_A
, ATTRIB_1
, ATTRIB_2
LIMIT v_batch_size;
--
-- long complicated comptuation ommitted
--
FOR i in 1 .. ID_TABLE_A.count() LOOP
-- long complicated comptuation ommitted
PIPE ROW (
test_data_rowType(
ID_TABLE_A(i)
, ATTRIB_1(i)
, ATTRIB_1(i)
));
END LOOP;
EXIT WHEN v_cursor%NOTFOUND;
END LOOP;
CLOSE v_cursor;
RETURN;
END;
FUNCTION test_data2
RETURN test_data_ids
PIPELINED
AS
BEGIN
for rec in (select * from TEST_SMALL_RESULTSET) LOOP
--
PIPE ROW (
test_data_rowType(
rec.ID_TABLE_A
, rec.ATTRIB_1
, rec.ATTRIB_2
));
END LOOP;
RETURN;
END;
FUNCTION test_data3
RETURN test_data_ids
AS
l_data test_data_ids := test_data_ids();
BEGIN
for rec in (select * from TEST_SMALL_RESULTSET) LOOP
--
l_data.extend;
l_data(l_data.count) :=
test_data_rowType(
rec.ID_TABLE_A
, rec.ATTRIB_1
, rec.ATTRIB_2
);
END LOOP;
RETURN l_data;
END;
END;
/
show errors
=================== crate and fill table with data
create table TEST_SMALL_RESULTSET(
ID_TABLE_A NUMBER,
ATTRIB_1 NUMBER,
ATTRIB_2 NUMBER
);
declare
c_size NUMBER := 10;
begin
for id IN 1 .. c_size
loop
insert into
TEST_SMALL_RESULTSET(ID_TABLE_A, ATTRIB_1, ATTRIB_2)
VALUES(id, dbms_random.value(1,10),
dbms_random.value(1,20));
end loop;
commit;
end;
/
--
Volker Apelt
Received on Thu Aug 17 2006 - 07:57:46 CDT
 |
 |