Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)
Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)
An index can indeed be created as part of a CREATE TABLE statement, which is true for option A. A descending index is treated as a function-based index in Oracle, making option C correct. Furthermore, it is possible to create both a UNIQUE and non-unique index on the same table column, so option E is accurate. It's worth noting that not all queries will necessarily use an index even if it filters on an indexed column due to considerations like query optimization and execution plans, making option B incorrect.
https://www.examtopics.com/discussions/oracle/view/9439-exam-1z0-071-topic-1-question-300-discussion/
A- CREATE TABLE Books( ID NUMBER PRIMARY KEY USING INDEX (CREATE INDEX idx_custom_books_id ON books (ID)), Title VARCHAR2(100) NOT NULL, Author VARCHAR2(100) NOT NULL, PublishYear NUMBER NOT NULL ); C- "Oracle Database treats descending indexes as if they were function-based indexes" https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__GUID-92F4F0FB-499A-4ED7-8630-B219F8A50B90 E- "When you have multiple indexes on the same set of columns, only one of these indexes can be visible at a time, and any other indexes must be invisible." "You can create multiple indexes on the same set of columns when at least one of the following index characteristics is different: The indexes have different uniqueness properties. You can create both a unique and a non-unique index on the same set of columns." https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-B4B30CF4-2B95-44D6-8596-EC2A378251EF Correct is ACE, according the above evidences.
you can create both unique and non unique on the same set of columns not on one column option E is saying on the same table column making it wrong
E is false: -- Create table CREATE TABLE test_table ( id NUMBER, value VARCHAR2(255) ); -- Populate the table INSERT INTO test_table (id, value) VALUES (1, 'One'); INSERT INTO test_table (id, value) VALUES (2, 'Two'); COMMIT; -- Create a unique index CREATE UNIQUE INDEX test_table_uniq_idx ON test_table(id); -- Create a non-unique index CREATE INDEX test_table_idx ON test_table(id); ORA-01408: such column list already indexed
Correct answer for me are: A, B, and C; conclusion got by exclusion and priority: D, E, and F are wrong with high priority for me! :-)
Correct answer in my opinion.
ACE for me
SQL> create index a1 on x1(b1); Index created. SQL> create unique index a2 on x1(b1); create unique index a2 on x1(b1) * ERROR at line 1: ORA-01408: such column list already indexed SQL>
C is correct: SQL> create table t1 (col1 number, col2 varchar2(10), col3 varchar2(10)); Table created. Elapsed: 00:00:00.03 SQL> create unique index ix1 on t1(col1); Index created. Elapsed: 00:00:00.02 SQL> create index ix2 on t1(col1, col2); Index created. Elapsed: 00:00:00.01 SQL> create unique index ix3 on t1(col1, col2, col3); Index created. Elapsed: 00:00:00.02 SQL>
TYPO: I mean option E.
ABE IS CORRECT A-TRUE B-TRUE C-FALSE D-FALSE: The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower. E-TRUE F-FALSE:Unlike unusable indexes, an invisible index is maintained during DML statements
C-FALSE: Function-based indexes: contain the precomputed value of a function/expression