Re: Need design suggestion , whether index organized table is good choice here

What about hash partitioning on your primary key COL1?

On Sat, 29 Jan 2022, 12:52 am Pap, <oracle.developer35_at_gmail.com> wrote:

> Hello Listers, It's a 11.2.0.4 oracle database. We have one table (say
> REF_TAB) which has four columns, two of them(i.e. COL1, masked_COL1) are
> actually holding business attributes and the other two are created_date and
> created_by_user columns. The length of those two business columns are
> varchar2(34 bytes) and varchar2(36 bytes) respectively. And both of these
> columns hold all unique/distinct values. Col1 is the one on which the
> primary key is defined. The table currently has ~1.5billion rows in it and
> its size is ~160GB. It is estimated to grow to hold ~5-8billion rows.
>
> The table is always being queried on a filter/join on COL1 and that too as
> an outer join and will fetch the value of masked_col1. So in short the
> requirement is to scan the full table data based on outer join on column
> col1 like ( TRAN_TAB2.ANBR=REF_TAB.COL1 (+) ). And below is a sample
> query sql monitor showing even that table is getting access using primary
> key index but still is consuming all the time(sql_plan_line_id 13 and 14
> below). As sql monitor shows , Is those 1hr+ DB time for the 14
> million times index unique scan justified? And making it to go for full
> using hints is chewing up ~100gb+ temp space too. I can't not think of any
> partitioning strategy which can help us here to get the table access
> faster, as we have to look up whole column data here for incoming value of
> COL1.
>
> So want to understand how the access path to this table can be made
> faster? Will index organized tables be suggested in such a scenario and
> will help us in this kind of requirement? Or any other design strategy
> required here?
>
>
> Global Information
> ------------------------------
> Status : EXECUTING
> Instance ID : 4
> SQL Execution ID : 67108973
> Execution Started : 01/27/2022 07:43:01
> First Refresh Time : 01/27/2022 07:43:05
> Last Refresh Time : 01/27/2022 09:04:19
> Duration : 4879s
> Fetch Calls : 2782
>
> Global Stats
>
> =================================================================================================================
> | Elapsed | Cpu | IO | Application | Concurrency | Cluster |
> Fetch | Buffer | Read | Read | Cell |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
> Calls | Gets | Reqs | Bytes | Offload |
>
> =================================================================================================================
> | 5469 | 2065 | 2464 | 0.01 | 0.00 | 940 |
> 2782 | 124M | 24M | 202GB | 9.09% |
>
> =================================================================================================================
>
>
> ==============================================================================================================================================================================================================================
> | Id | Operation | Name
> | Rows | Cost | Time | Start | Execs | Rows | Read | Read
> | Cell | Mem | Activity | Activity Detail |
> | | |
> | (Estim) | | Active(s) | Active | | (Actual) | Reqs |
> Bytes | Offload | | (%) | (# samples)
> |
>
> ==============================================================================================================================================================================================================================
> | 0 | SELECT STATEMENT |
> | | | 4875 | +4 | 1 | 14M | |
> | | | 1.11 | Cpu (53) |
> | 1 | NESTED LOOPS OUTER |
> | 166K | 1M | 4875 | +4 | 1 | 14M | |
> | | | 0.04 | Cpu (2) |
> | 2 | VIEW |
> | 166K | 901K | 4875 | +4 | 1 | 14M | |
> | | | 0.40 | Cpu (19) |
> | 3 | NESTED LOOPS OUTER |
> | 166K | 901K | 4875 | +4 | 1 | 14M | |
> | | | 0.04 | Cpu (2) |
> | 4 | HASH JOIN |
> | 166K | 402K | 4875 | +4 | 1 | 14M | |
> | | 1M | 0.29 | Cpu (14) |
> | 5 | JOIN FILTER CREATE | :BF0000
> | 1836 | 66073 | 1 | +4 | 1 | 1890 | |
> | | | | |
> | 6 | TABLE ACCESS STORAGE FULL | STAGE_TAB
> | 1836 | 66073 | 5 | +0 | 1 | 1890 | |
> | | | 0.02 | gc cr multi block request (1) |
> | 7 | JOIN FILTER USE | :BF0000
> | 48M | 336K | 4875 | +4 | 1 | 15M | |
> | | | 0.13 | Cpu (6) |
> | 8 | PARTITION RANGE SINGLE |
> | 48M | 336K | 4875 | +4 | 1 | 15M | |
> | | | 0.04 | Cpu (2) |
> | 9 | TABLE ACCESS STORAGE FULL | TRAN_TAB
> | 48M | 336K | 4878 | +1 | 1 | 15M | 11748 | 11GB
> | 92.40% | 7M | 0.25 | Cpu (11) |
> | | |
> | | | | | | | |
> | | | | cell smart table scan (1) |
> | 10 | PARTITION RANGE SINGLE |
> | 1 | 4 | 4875 | +4 | 14M | 2M | |
> | | | 0.53 | Cpu (25) |
> | 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TRAN_TAB2
> | 1 | 4 | 4875 | +4 | 14M | 2M | 433K |
> 3GB | | | 2.52 | gc cr grant 2-way (26) |
> | | |
> | | | | | | | |
> | | | | Cpu (57) |
> | | |
> | | | | | | | |
> | | | | gcs drm freeze in enter server mode (2) |
> | | |
> | | | | | | | |
> | | | | cell single block physical read (35) |
> | 12 | INDEX RANGE SCAN | TRAN_TAB2_IX1
> | 1 | 3 | 4875 | +4 | 14M | 2M | 271K |
> 2GB | | | 2.40 | gc cr grant 2-way (18) |
> | | |
> | | | | | | | |
> | | | | Cpu (63) |
> | | |
> | | | | | | | |
> | | | | cell single block physical read (33) |
> | 13 | TABLE ACCESS BY INDEX ROWID | REF_TAB
> | 1 | 3 | 4877 | +2 | 14M | 13M | 11M |
> 88GB | | | 34.01 | gc cr block 2-way (1)
> |
> | | |
> | | | | | | | |
> | | | | gc cr disk read (6) |
> | | |
> | | | | | | | |
> | | | | Cpu (397) |
> | | |
> | | | | | | | |
> | | | | cell single block physical read (1214) |
> | -> 14 | INDEX UNIQUE SCAN | REF_TAB_PK
> | 1 | 2 | 4876 | +4 | 14M | 13M | 12M | 89GB
> | | | 57.09 | gc cr block 2-way (154) |
> | | |
> | | | | | | | |
> | | | | gc cr block 3-way (14) |
> | | |
> | | | | | | | |
> | | | | gc cr block busy (520) |
> | | |
> | | | | | | | |
> | | | | gc cr disk read (30) |
> | | |
> | | | | | | | |
> | | | | gc cr failure (1) |
> | | |
> | | | | | | | |
> | | | | gc cr grant 2-way (106) |
> | | |
> | | | | | | | |
> | | | | gc current block 2-way (4) |
> | | |
> | | | | | | | |
> | | | | gc current grant 2-way (3) |
> | | |
> | | | | | | | |
> | | | | gc remaster (2) |
> | | |
> | | | | | | | |
> | | | | Cpu (754) |
> | | |
> | | | | | | | |
> | | | | gcs drm freeze in enter server mode (6) |
> | | |
> | | | | | | | |
> | | | | latch: object queue header operation (1) |
> | | |
> | | | | | | | |
> | | | | cell single block physical read (1121) |
>
> ==============================================================================================================================================================================================================================
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 4 - access("TRAN_TAB"."SBID"="SFID")
> 6 - storage(("PART_DATE"=:B1 AND "ASP_NM"=:B2))
> filter(("PART_DATE"=:B1 AND "ASP_NM"=:B2))
> 9 - storage(("TRAN_TAB"."PART_DATE1"=:B1 AND
> SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID")))
> filter(("TRAN_TAB"."PART_DATE1"=:B1 AND
> SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID")))
> 11 - filter("TRAN_TAB2"."PART_DATE1"=:B1)
> 12 - access("TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID" AND
> "TRAN_TAB2"."P_CD"='XX')
> 14 - access("TRAN_TAB2"."ANBR"="REF_TAB"."COL1")
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l