Oracle where in + like

[Valcha]

Sveiki! Man nepieciešams atlasīt ierakstus, kuru kolonna col1 vērtība var sākties ar jebkuriem 2 cipariem no saraksta.

Gribēju rakstīt kaut kā tā:

 

select col2,col3 from tabula where col1 like in ('10%','20%').

 

Diemžēl nesanāk. Vai vispār to var kaut kā izdarīt? Zinu, ka var jau rakstīt where (col1='10%' or col1='20%'), bet, pirmkārt, man tā jau atlase uz pusdienu (šī, protams, ir tikai daļa no mega vaicājuma), negribu sarežģīt, otrkārt, arī nesmuki.

Nu, varbūt kāds ir ko līdzīgu darījis? Jau pusstundu meklēju gogli, eksperimentēju utt.

[andrisp]

moshka

WHERE SUBSTR(col1, 0, 2) IN ('10', '20', ...)

 

SUBSTR sintaksi paskaties manuālī. Es tikai minēju.

[Valcha]

Nu jā, substr ideja man bija prātā, bet tomēr tā ir papildus rēķināšana, kas iebremzēs uz miljoniem ierakstu skaitļošanas. Bet varbūt, ka patiešām ar substr būs jāstrādā, ja nekas labāks neatradīsies.

[andrisp]

Nu LIKE man liekas būtu vēl bremzīgāks par SUBSTR.

 

Un vispār - ja būs miljoniem ierakstu, tad iesaku izveidot atsevišķu indeksētu lauku, kas saturēs tos pirmos divus simbolus. Un tad meklēt pēc tā.

[Aleksejs]

Varbūt (bet tas ir jānotestē), ja nav iespēja izveidot papildkolonnu, ātrāk būs ar izveidotu skatu - VIEW, kurā atlasīts tas SUBSTR, jo pēc idejas darbībai ar VIEW būtu it kā jābūt ātrākam...

[Gints Plivna]

Ja Tev tā jau ir mega selekts, kur piemēram šai pašā tabulā pārbauda citas vērtības, tad esmu par 99% pārliecināts, ka tas liekasi substr neko Tev tur daudz neietekmēs. Ja šitais nosacījums ir kādā citā tabulā, no kuras varētu sākt meklēt ierakstus un ievērojami to skaitu ierobežot, tad, protams, kaut kas indexveidīgs varētu labi noderēt. Te nu lūk varam atcerēties par FBI - function based index.

Skatamies piemēru:

SQL> create table qaqa(a varchar2(100));
Table created.
SQL> insert into qaqa select object_name from dba_objects;
73779 rows created.
SQL> insert into qaqa select * from qaqa;
73779 rows created.
SQL> /
147558 rows created.
SQL> /
295116 rows created.
SQL> /
590232 rows created.
SQL> /
1180464 rows created.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> select * from qaqa where substr(a, 1, 2) in ('10', '20');
no rows selected
Elapsed: 00:00:01.50
SQL> set autot on
SQL> /
no rows selected
Elapsed: 00:00:01.46
Execution Plan
----------------------------------------------------------
  0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2809 Card=111 Bytes=5772)
  1	0   TABLE ACCESS (FULL) OF 'QAQA' (TABLE) (Cost=2809 Card=111  Bytes=5772)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
   9633  consistent gets
	  0  physical reads
	  0  redo size
	258  bytes sent via SQL*Net to client
	377  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

Tātad kā redzam full skans. Un laiks apmēram 1,4 sekundes.

Tagad uzraujam indexu:

SQL> create index q1_idx on qaqa(substr(a, 1, 2));
Index created.
Elapsed: 00:00:13.48
SQL> select * from qaqa where substr(a, 1, 2) in ('10', '20');
no rows selected
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
  0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=791 Card=26603 Bytes=1542974)
  1	0   INLIST ITERATOR
  2	1	 TABLE ACCESS (BY INDEX ROWID) OF 'QAQA' (TABLE) (Cost=791 Card=26603 Bytes=1542974)
  3	2	   INDEX (RANGE SCAN) OF 'Q1_IDX' (INDEX) (Cost=22 Card=10641)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  6  consistent gets
	  0  physical reads
	  0  redo size
	258  bytes sent via SQL*Net to client
	377  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

 

BET, protams, jāatceras, ka šo indexu būs jāuztur insert, update, delete un merge operācijām, tā kā tādu ir vērts tikai taisīt tad, ja selektos ieguvums ir lielāks nekā IUDM.

 

Tagad, kas atiecas uz view (skatījumiem). Principā visdoršāk, protams, būtu vienkārši pārbaudīt, bet es tomēr ļoti šaubos, ka view kaut ko dos. Jo view nav nekas vairāk kā saglabāts SELECT teikums datubāzē ar nosaukumu, lai, piemēram, nebūtu jāraksta gari SQL teikumi, ierobežotu tiesības utml. Tiesa gan ir materializētie skatījumi (materialized view), kam gan ir pašiem savas tabulas apakšā.

 

Gints Plivna

http://datubazes.wordpress.com

[Gints Plivna]

Ja un ja izveido vienkāršu indexu, tad protams, tas nepalīdzēs substr, bet palīdzēs šai konstrukcija like '10%' or like '20%'.

 

Vienkāršs indekss + substr = full scan

 

SQL> create index q_idx on qaqa(a);
Index created.
Elapsed: 00:00:40.53
SQL> select * from qaqa where substr(a, 1, 2) in ('10', '20');
no rows selected
Elapsed: 00:00:01.42
Execution Plan
----------------------------------------------------------
  0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2809 Card=111 Bytes=5772)
  1	0   TABLE ACCESS (FULL) OF 'QAQA' (TABLE) (Cost=2809 Card=111 Bytes=5772)
Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
   9717  consistent gets
	  0  physical reads
	  0  redo size
	258  bytes sent via SQL*Net to client
	377  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

 

Vienkāršs indekss + like un or = index range scan cik reizes vajag + rezultātu konkatenācija.

 

SQL> select * from qaqa where a like '10%' or a like '20%';
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
  0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=8 Bytes=416)
  1	0   CONCATENATION
  2	1	 INDEX (RANGE SCAN) OF 'Q_IDX' (INDEX) (Cost=105 Card=4 Bytes=208)
  3	1	 INDEX (RANGE SCAN) OF 'Q_IDX' (INDEX) (Cost=105 Card=4 Bytes=208)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  6  consistent gets
	  0  physical reads
	  0  redo size
	258  bytes sent via SQL*Net to client
	377  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

 

Gints

 

P.S. Vairāk par autotrace skat šeit