Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nikhil.

Asked: November 22, 2016 - 10:47 am UTC

Last updated: November 23, 2016 - 5:20 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi,

I have table test_tab having billins of data.
One of the column used in below query is TEST_KEY with datatype VARCHAR2(76).

I have a requiremnt to fecth data from particular start and end position of string whch i sthere WHERE clause below.

Could you please help how can I get rid of FULL TABLE SCAN, or any way spliting query.

SELECT (CASE
WHEN (SUBSTR(TEST_KEY,5,8) = '00027000' AND SUBSTR(TEST_KEY, 21, 8) = '00010001' ) THEN
(CASE WHEN (FK_TB_SITE IS NULL) THEN '0' ELSE '2' end)
WHEN (SUBSTR(TEST_KEY,5,8) = '00027000' AND SUBSTR(TEST_KEY, 21, 8) = '00010001' AND SUBSTR(TEST_KEY, 29, 8) = '00027000') THEN
(CASE when (FK_TB_SITE IS NULL) then '0' ELSE '2' end)
ELSE '1' END
)"ERROR_MESSAGE",
SUBSTR(TEST_KEY, 5, 8) BANK ,
SUBSTR(TEST_KEY, 21, 8) LEVEL1,
SUBSTR(TEST_KEY, 29, 8) LEVEL2,
FK_TB_SITE
FROM test_tab
WHERE SUBSTR(TEST_KEY,5,8) = '00007101' AND
SUBSTR(TEST_KEY, 21, 8) = '00010001' AND
SUBSTR(TEST_KEY, 29, 8) = '00027000';



Plan hash value: 2961360322

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 8610 (1)| 00:01:44 |
|* 1 | TABLE ACCESS FULL| test_tab | 1 | 78 | 8610 (1)| 00:01:44 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SUBSTR("TEST_KEY",5,8)='11' AND
SUBSTR("TEST_KEY",21,8)='12' AND SUBSTR("TEST_KEY",29,8)='13')

and Chris said...

To avoid a full tablescan you need a index on at least one of the expressions in the where clause. Preferably all three. For example:

create index i on tab (
  SUBSTR(TEST_KEY,5,8), SUBSTR(TEST_KEY, 21, 8), SUBSTR(TEST_KEY, 29, 8)
);


Of course, this doesn't guarantee that Oracle will use the index. But at least it makes it possible.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4433887271030

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Already have function based index created

A reader, November 22, 2016 - 11:53 am UTC

Thanks for your reply Chris .

creating more indexes will create problem in DML..
Could you please share your views..

CREATE INDEX "IDX_CL_TB_UNIT_1" ON "TB_UNIT" ("TEST_KEY", UPPER("UNIT_NAME"), NVL("PURGEID",'-1'))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;

CREATE INDEX "IDX_SUBSTR_PROCESSOR_KEY" ON "TB_UNIT" (SUBSTR("TEST_KEY",24,53))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;

CREATE INDEX "IDX_TB_UNIT_1" ON "TB_UNIT" ("UNIT_ID", "PURGEID", "TEST_KEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;

CREATE INDEX "IDX_TB_UNIT_TBR" ON "TB_UNIT" (SUBSTR("TEST_KEY",24), "BANK_NBR", NVL("PURGEID",'-1'))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;

CREATE UNIQUE INDEX "XAK1TB_UNIT" ON "TB_UNIT" ("TEST_KEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 66060288 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_DATA" ;
Chris Saxon
November 22, 2016 - 6:03 pm UTC

Are those the existing indexes? If so none of the match your where clause. So Oracle won't use them! The expression in your index must that in your where clause exactly.

Yes, adding more indexes increases the DML overheads. So it's a balancing act. Which do you need more, fast queries or fast DML?

But as always, measure! See what impact adding an index has on DML and your query. Then make your judgement based on these results.

Suspicious requirement

Andrew Sayer, November 22, 2016 - 8:20 pm UTC

This looks to me very much like you are trying to squeeze as much data into the same column as possible. Why not use different columns to populate the different data?
What does SUBSTR(TEST_KEY,5,8) represent? A lookup to bank data?

Not only will your queries be much easier to write, you can individually index each column (if necessary). The optimizer will behave much nicer for you as it will know for e.g. that one column should only have a two values so an index might not be useful (as you'll get half the table back) or one column has so many different values that an index would be the perfect choice.
You can use foreign keys to enable referential integrity of your data
You won't waste as much storage.
Connor McDonald
November 23, 2016 - 5:20 am UTC

good input

Virtual column

Ghassan, November 23, 2016 - 4:51 am UTC

Consider adding vc as concat of the 3 predicates.
Moreover if the table is not partitioned do it then by hash on that col.
Consider also indexing that col globally or locally if partitioned.

I prefer the hash partitioning in this case to allow wp. Parallel will be checked if more efficient execution. Anyway tune and tune...

Supplementary

Ghassan, November 23, 2016 - 5:01 am UTC

..and why not using vc as Ora_hash (concat (SUBSTR(TEST_KEY,5,8), SUBSTR(TEST_KEY, 21, 8), SUBSTR(TEST_KEY, 29, 8) ))

Predicate then will be ...
Where My_vc = ora_hash (concat (:1, :2, :3))
..


Parallel Query

Rajeshwaran, Jeyabal, November 23, 2016 - 12:01 pm UTC

....
I have table test_tab having billins of data.
....


Since you have a huge data set to deal with, why not just you a Parallel Query in place?

I hope your predicates match up with a huge datasets in table, hence the optimizer has ignore the index in place.

demo@ORAST01> $type d:\script.sql
set serveroutput off
set termout off
select /*+ gather_plan_statistics parallel(big_table,4) */ owner,count(*)
from big_table
group by owner
order by 2 desc;
set termout on
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

demo@ORAST01> @d:\script.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  gy1qtygx34xxm, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(big_table,4) */
owner,count(*) from big_table group by owner order by 2 desc

Plan hash value: 2986679575

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |     27 |00:00:10.91 |      22 |
|   1 |  PX COORDINATOR                     |           |      1 |        |     27 |00:00:10.91 |      22 |
|   2 |   PX SEND QC (ORDER)                | :TQ10002  |      0 |     27 |      0 |00:00:00.01 |    0 |
|   3 |    SORT ORDER BY                    |           |      0 |     27 |      0 |00:00:00.01 |    0 |
|   4 |     PX RECEIVE                      |           |      0 |     27 |      0 |00:00:00.01 |    0 |
|   5 |      PX SEND RANGE                  | :TQ10001  |      0 |     27 |      0 |00:00:00.01 |    0 |
|   6 |       HASH GROUP BY                 |           |      0 |     27 |      0 |00:00:00.01 |    0 |
|   7 |        PX RECEIVE                   |           |      0 |     27 |      0 |00:00:00.01 |    0 |
|   8 |         PX SEND HASH                | :TQ10000  |      0 |     27 |      0 |00:00:00.01 |    0 |
|   9 |          HASH GROUP BY              |           |      0 |     27 |      0 |00:00:00.01 |    0 |
|  10 |           PX BLOCK ITERATOR         |           |      0 |    100M|      0 |00:00:00.01 |    0 |
|* 11 |            TABLE ACCESS STORAGE FULL| BIG_TABLE |      0 |    100M|      0 |00:00:00.01 |    0 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 4 because of table property


33 rows selected.

demo@ORAST01>