Categories:
.NET (961)
C (387)
C++ (185)
CSS (84)
DBA (8)
General (31)
HTML (48)
Java (641)
JavaScript (220)
JSP (109)
JUnit (31)
MySQL (297)
Networking (10)
Oracle (562)
Perl (48)
Perl (9)
PHP (259)
PL/SQL (140)
RSS (51)
Software QA (28)
SQL Server (5)
Struts (20)
Unix (2)
Windows (3)
XHTML (199)
XML (59)
Other Resources:
How To Create a Single Index for Multiple Columns
How To Create a Single Index for Multiple Columns? - Oracle DBA FAQ - Managing Oracle Table Indexes
✍: FYIcenter.com
If you know a group of multiple columns will be always used together as search criteria, you should create a single index for that group of columns with the "ON table_name(col1, col2, ...)" clause. Here is an example of one index for two columns:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, birth_date DATE NOT NULL, social_number VARCHAR(80) UNIQUE NOT NULL); Table created. CREATE INDEX student_names ON student(first_name,last_name); Index created. SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME UNIQUENES ----------------------- --------------------- --------- SYS_C004123 STUDENT UNIQUE SYS_C004124 STUDENT UNIQUE STUDENT_NAMES STUDENT NONUNIQUE SELECT index_name, table_name, column_name FROM USER_IND_COLUMNS WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME COLUMN_NAME -------------------- ---------------- ---------------- SYS_C004135 STUDENT ID SYS_C004136 STUDENT SOCIAL_NUMBER STUDENT_NAMES STUDENT FIRST_NAME STUDENT_NAMES STUDENT LAST_NAME
The USER_IND_COLUMNS view confirms that index STUDEND_NAMES has two columns FIRST_NAME and LAST_NAME.
2007-04-27, 4868👍, 0💬
Popular Posts:
How To Control Horizontal Alignment? - XHTML 1.0 Tutorials - Understanding Tables and Table Cells By...
What are the core functionalities in XML .NET framework? Can you explain in detail those functionali...
How will you freeze the requirement in this case? What will be your requirement satisfaction criteri...
What will be printed as the result of the operation below: #define swap(a,b) a=a+b;b=a-b;a=a-b; void...
.NET INTERVIEW QUESTIONS - What are types of compatibility in VB6? There are three possible project ...