Apakah indeks yang ada pada Oracle?

Jenis indeks Oracle termasuk: indeks bukan unik, indeks unik, indeks bitmap, indeks partition awalan tempatan, indeks partition bukan awalan tempatan, indeks partition awalan global, indeks partition hash, indeks berasaskan fungsi . Indeks perlu dibuat selepas memasukkan data ke dalam jadual Indeks unik boleh dibuat dengan pernyataan "buat indeks unik".

Persekitaran pengendalian tutorial ini: sistem Windows 7, versi Oracle 11g, komputer Dell G3.

Apakah itu indeks?

• Indeks ialah objek tambahan yang dibina pada satu atau lebih lajur jadual, dengan tujuan mempercepatkan akses kepada data dalam jadual; Indeks storan Oracle Struktur data ialah pokok B* (pokok seimbang), dan perkara yang sama berlaku untuk indeks bitmap, kecuali nod daun mempunyai indeks nombor B* yang berbeza
• Indeks terdiri daripada nod akar, nod cawangan dan nod daun, dan peringkat atas Blok indeks mengandungi data indeks blok indeks bawahan, dan nod daun mengandungi data indeks dan rowid yang menentukan lokasi sebenar baris.

Penerangan Indeks

1) Indeks ialah salah satu objek pangkalan data, digunakan untuk mempercepatkan pengambilan data, serupa dengan indeks buku. Pengindeksan dalam pangkalan data boleh mengurangkan jumlah data yang perlu dibaca oleh program pangkalan data semasa menanyakan hasil, sama seperti cara dalam buku kita boleh menggunakan indeks untuk mencari maklumat yang kita inginkan tanpa perlu membaca keseluruhan buku.

2) Indeks ialah objek pilihan yang dibina di atas meja; kunci kepada indeks adalah untuk menggantikan kaedah pengambilan imbasan jadual penuh lalai dengan satu set kunci indeks yang diisih, dengan itu meningkatkan kecekapan mendapatkan semula

3) Indeks secara logik dan fizikal bebas daripada jadual dan data yang berkaitan Apabila membuat atau memadam indeks, ia tidak akan menjejaskan jadual asas; pada jadual Semasa operasi DML (seperti semasa menjalankan operasi berkaitan sisipan, pengubahsuaian atau pemadaman), Oracle akan menguruskan indeks secara automatik dan pemadaman indeks tidak akan menjejaskan jadual

5) Indeks telus kepada pengguna , tanpa mengira jadual sama ada terdapat indeks, penggunaan pernyataan sql kekal tidak berubah

6) Oracle akan secara automatik mencipta indeks pada lajur apabila mencipta kunci utama

Tujuan menggunakan indeks:

Mempercepatkan pertanyaan

Kurangkan operasi I/O

• Hapuskan pengisihan cakera (indeks boleh mempercepatkan pengisihan)
• Bila menggunakan indeks:

Bilangan rekod yang dikembalikan oleh pertanyaan

Jadual mempunyai banyak serpihan (penambahan dan pemadaman yang kerap)

• Jenis indeks

Indeks bukan unik (paling biasa digunakan)

Indeks unik

• Indeks bitmap
• Indeks partition awalan separa
• Indeks partition bukan awalan separa
• Indeks sekatan awalan global
• Indeks sekatan cincang
• Indeks berasaskan fungsi
• Garis panduan untuk menguruskan indeks

Buat indeks selepas memasukkan data ke dalam jadualSelepas memasukkan atau memuatkan data dengan SQL*Loader atau Alat , ia adalah lebih berkesan untuk mencipta indeks; Kerap mendapatkan 40% baris dalam jadual besar yang diisih atau 7% daripada jadual tidak diisih, disyorkan Bina indeks

Untuk menambah baik perkaitan berbilang jadual, lajur indeks digunakan untuk cantuman; >
• Nilai dalam lajur agak unik;
• Julat nilai (besar: Indeks pokok B*, kecil: indeks bitmap import
Lajur jenis tarikh biasanya sesuai untuk indeks berasaskan fungsi; lajur

mempunyai banyak nilai nol dan tidak sesuai untuk pengindeksan

Susun lajur indeks untuk prestasi

• Berbilang medan sering digunakan bersama untuk mendapatkan semula rekod, dan indeks gabungan adalah lebih cekap daripada indeks tunggal
• Letakkan lajur yang paling biasa digunakan di hadapan, contohnya:
• , gunakan
atau
• dalam keadaan
dan pertanyaan akan menggunakan indeks Jika hanya medan
• digunakan, indeks akan menjadi tidak sah;
Gabung/Pisah indeks yang tidak diperlukan.

Hadkan bilangan indeks setiap jadual

• Sesuatu jadual boleh mempunyai ratusan indeks (adakah anda akan melakukan ini?) , tetapi untuk kerap memasukkan dan mengemas kini jadual, semakin banyak indeks yang dimiliki oleh CPU sistem, semakin berat beban I/O;
• dx_groupid_serv_id(groupid,serv_id)wheregroupidPadamkan indeks yang tidak diperlukan lagi groupid,serv_idserv_id

Indeks tidak sah, terutamanya apabila menggunakan indeks atau bit berasaskan fungsi Graf indeks, tetapi menggunakan indeks pokok B*;

Pertanyaan dalam aplikasi tidak menggunakan indeks; membina semula indeks, jika anda menggunakan indeks alter...bina semula untuk membina semula indeks, anda tidak perlu memadamkan indeks.

• Penggunaan ruang blok data indeks
  • Nyatakan ruang jadual semasa membuat indeks, terutamanya apabila membuat kunci utama Ruang jadual hendaklah dinyatakan dengan jelas;
  • Anggarkan saiz indeks dan tetapkan parameter storan dengan munasabah, lalai kepada saiz ruang jadual atau tetapkan awal dan di sebelah saiz yang sama.
  Pertimbangkan untuk mencipta indeks secara selari
  Penciptaan indeks selari boleh digunakan untuk jadual besar Apabila mencipta indeks secara selari, parameter storan diubah oleh setiap pelayan pertanyaan Proses digunakan secara berasingan, contohnya:
  ialah
  • , dan tahap keselarian ialah initial, maka sekurang-kurangnya 1M ruang akan digunakan semasa penciptaan indeks; 88M pertimbangkan untuk menggunakan
  Cipta indeks

  nologgingAnda boleh menggunakan nologging untuk mengurangkan log buat semula semasa membuat indeks untuk jadual besar; fail log;

  Memendekkan masa Pengindeksan penciptaan;
  • Bagaimana untuk mencipta indeks terbaik?
  • Buat indeks secara eksplisit

  Buat berasaskan fungsi Indeks: biasanya digunakan dalam pengelasan fungsi seperti , contohnya:

  Cipta indeks bitmap:

  create index index_name on table_name(field_name)
  　　tablespace tablespace_name
  　　pctfree 5
  　　initrans 2
  　　maxtrans 255
  　　storage
  　　(
  　　minextents 1
  　　maxextents 16382
  　　pctincrease 0
  　　);

  untuk pangkalan kecil dan secara relatif pangkalan stabil Semasa mengindeks lajur, anda harus terlebih dahulu mempertimbangkan indeks bitmap, contohnya:

  UPPER、LOWER、TO_CHAR(date) Secara eksplisit mencipta indeks unik

  create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

  Anda boleh menggunakan pernyataan

  untuk mencipta indeks unik, contohnya:

  Buat indeks yang berkaitan dengan kekangan

  create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

  Anda boleh menggunakan frasa

  untuk mencipta indeks bagi medan yang berkaitan dengan kekangan

  dan create unique index, contohnya:

  create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

  Cara membuat indeks separa separa

  using indexJadual asas mestilah jadual sekatan; jadual; uniqueprimary keyBilangan sub-partition untuk setiap partition indeks Sama seperti partition jadual asas yang sepadan

  alter table table_name
  　　add constraint PK_primary_keyname primary key (field_name)
  　　using index tablespace tablespace_name；

  Entri indeks baris dalam sub-partition jadual asas disimpan; dalam sub-partition indeks yang sepadan, sebagai contoh:
  • Cara mencipta indeks global terbahagi julat
  • Jadual asas boleh menjadi jadual global dan meja sekat.
  Membina semula indeks sedia ada
  • Detik semasa membina semula indeks sedia ada tidak akan menjejaskan pertanyaan
  Membina semula indeks boleh memadamkan blok data tambahan; kecekapan pertanyaan indeks ;

  　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
  　　Pctfree 5　　Tablespace TBS_AK01_IDX
  　　Storage (
  　　MaxExtents 32768　　PctIncrease 0　　FreeLists 1　　FreeList Groups 1　　)
  　　local　　/

  Untuk indeks terbahagi:

  create index idx_start_date on tg_cdr01(start_date)
  　　global partition by range(start_date)
  　　(partition p01_idx vlaues less than (‘0106’)
  　　partition p01_idx vlaues less than (‘0111’)
  　　…
  　　partition p01_idx vlaues less than (‘0401’ ))
  　　/

  Sebab untuk memadamkan indeks

  
  

  Indeks tidak diperlukan lagi;

  
  Pertanyaan Indeks yang dikeluarkan terhadap jadual berkaitannya tidak memberikan peningkatan prestasi yang dijangkakan; ;

  alter index idx_name rebuild nologging;

  Indeks telah menjadi terlalu berpecah-belah dan mesti dipadamkan sebelum membina semula; >

  alter index idx_name rebuild partition partiton_name nologging;

  Kos untuk menubuhkan indeks
  • Apabila mengekalkan jadual asas, sistem mesti mengekalkan indeks pada masa yang sama. Indeks yang tidak munasabah akan memberi kesan serius kepada sumber sistem, terutamanya pada CPU dan I/O; >Sesuatu jadual mempunyai Terdapat berjuta-juta keping data dan indeks ditambahkan pada medan tertentu, tetapi prestasi pertanyaan tidak bertambah baik Ini mungkin disebabkan terutamanya oleh pengehadan indeks
  . Indeks
  • mempunyai beberapa sekatan indeks Apabila sekatan indeks ini berlaku, walaupun indeks telah ditambahkan,
  masih akan melakukan imbasan jadual penuh dan prestasi pertanyaan tidak akan menjadi lebih baik. daripada tanpa indeks itu telah dipertingkatkan, tetapi prestasi mungkin lebih teruk disebabkan oleh overhed sistem untuk mengekalkan indeks dalam pangkalan data.
  • <br/> drop index idx_name; <br/> drop index idx_name drop partition partition_name; <br/>Pengetahuan lanjutan: Isu pengehadan indeks biasa

  1. Gunakan operator ketaksamaan (<>, != )

  Dalam kes berikut, walaupun terdapat indeks pada lajur

  , pernyataan pertanyaan masih melakukan imbasan jadual penuh

  db file sequential readTetapi pertanyaan sedemikian sememangnya diperlukan dalam pembangunan, adakah tiada penyelesaian kepada masalah tersebut?

  Ya! oracle

  Dengan menggantikan tanda ketaksamaan dengan sintaks atau untuk pertanyaan, anda boleh menggunakan indeks untuk mengelakkan imbasan jadual penuh: tukar pernyataan di atas kepada yang berikut dan anda boleh menggunakan indeks.

  oracleoracle

  2. Menggunakan adalah batal atau tidak batal

  Menggunakan atau juga akan mengehadkan penggunaan indeks kerana pangkalan data tidak ditakrifkan

  Nilai. Jika terdapat banyak null dalam lajur diindeks, indeks tidak akan digunakan (melainkan indeks adalah indeks bitmap, yang akan diterangkan secara terperinci dalam artikel blog akan datang). Menggunakan

  dalam pernyataan sql boleh menyebabkan banyak masalah. Cara untuk menyelesaikan masalah ini ialah: semasa membuat jadual, tentukan lajur yang perlu diindeks sebagai bukan nol (

  )

  dept_id

  3

  select * from dept where staff_num <> 1000;

  Jika indeks berasaskan fungsi tidak digunakan, maka menggunakan fungsi pada lajur diindeks dalam klausa

  akan menyebabkan pengoptimum mengabaikan indeks ini. Pertanyaan berikut tidak akan menggunakan indeks:

  select * from staff where trunc(birthdate) = '01-MAY-82';

  但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

  select * from staff where birthdate < (to_date(&#39;01-MAY-82&#39;) + 0.9999);

  4、比较不匹配的数据类型

  比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

  select * from dept where dept_id = 900198;

  这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

  select * from dept where dept_id = '900198';

  5、使用like子句

  使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

  Like 的字符串中第一个字符如果是‘%’则用不到索引

  Column1 like ‘aaa%’ 是可以的
  Column1 like ‘%aaa%’用不到

  6、使用IN

  尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多

  用In还是用Exists的时机

  当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists
  例：

  select count(*) from person_info where xb in (select xb_id from dic_sex);
  
  Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);
  
  Select * from person_info where zjhm=3101….;--将会对person_info全表扫描
  
  Select * from person_info where zjhm =‘3101…’;--才能用到索引

  假定TEST表的dt字段是date类型的并且对dt建了索引。
  如果要查‘20041010’一天的数据.下面的方法用不到索引

  Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

  而以下将会用到索引。

  select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

  7、如果能不用到排序，则尽量避免排序。

  用到排序的情况有
  集合操作。Union ,minus ,intersect等,注：union all 是不排序的。

  Order byGroup byDistinctIn

  有时候也会用到排序
  确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

  在排序的字段上创建索引，让排序在内存中执行，加快排序速度。
  

  8、在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。

  解决：执行表分析。获取表的最新信息。

  9、获取的数据量过大，全部扫描效率更高

  10、索引字段的值分散率太低，值太集中，如类型字段都是1,2， 状态类型Y-有效/N-无效。这类型的字段最好别建索引。

  尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。

  推荐教程：《Oracle教程》

Atas ialah kandungan terperinci Apakah indeks yang ada pada Oracle?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!