A, When changing a UNIQUE index to a non-unique index, you need to keep the following points in mind:
UNIQUE indexes implicitly created by primary key or UNIQUE constraints cannot be altered to non-unique indexes unless the constraint is dropped.
If a UNIQUE index is referenced by a foreign key constraint, you cannot change that index to a non-unique index.
When altering a partitioned index to a non-unique index, uniqueness must be maintained across all partitions.
regarding C the owner of the table must grant the INDEX privilege on the table to another user allowing that user to create an index on the table.
In general, it is best practice to keep tables and indexes owned by the same user. This will help to ensure the security of your data.
A. A UNIQUE index can be altered to be non-unique --> False
B. A SELECT statement can access one or more indices without accessing any tables. True
C. A table belonging to one user can have an index that belongs to a different user --> False, schema is still the owner.
D. An update to a table can result in updates to any or all of the table's indexes. True, it depends if the update goes on column with index or without.
E. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped. --> False, indexes will follow the table.
F. An update to a table can result in no updates to any of the table's indexes. True, you do an update on a single column that has no index.
C is OK. Here is the test.
As sys user grant:
GRANT CREATE ANY INDEX TO HR;
As hr user:
CREATE INDEX cust_first_name_idx ON oe.customers (cust_first_name);
Index CUST_FIRST_NAME_IDX created.
As user sys:
SELECT D.OWNER, D.table_owner, D.table_name FROM dba_indexes D WHERE D.index_name = 'CUST_FIRST_NAME_IDX';
"OWNER" "TABLE_OWNER" "TABLE_NAME"
"HR" "OE" "CUSTOMERS"
B is right, when you use a Fast Full Index Scan.
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html#GUID-2C8A7262-DEBE-4932-8EFB-64E61AD041C1
For Me is B , E and F
A. is wrong ,because i have to drop the index and then create it again
B. is correct since Index are phisically and logically indipendent from data to which they refer
C. is Wrong , Index are schema object so is not possible that a table on a schema use an index created on another schema.
D is wrong , it follow B. Indexes are independent from data they just speed up the retrieve process.
E. is Correct , when we drop a table then its indexes are dropped as well
F. is correct due to its logical and physical indipendent nature then an update can result in a no update on any indexes of a table.
I think the biggest confusion here is given by option D.
While A is false (can't change the uniqueness of an index) as E (Indexes of a dropped table are moved to the recycled bin if enabled)
B is true if you select more indexed columns on different indexes and no unindexed columns.
C is true for sure cause it's possible, given the right privileges, to create an index as schema X on a table owned by schema Y.
F Is true cause no index may be updated, if you update a non-indexed column and the row address is unchanged
D also sounds right, I think the key might be in the phrasing here: if it's intended that the update of any or all indexes are the only possible outcome of a table update, then it would in fact be false. Otherwise, as someone else noted, I also suspect that more answers may be right, even though you only have to select three.
The English translation of the given text is as follows:
Choice D appears to be correct, but when considering choice F:
"When a table is updated," indexes may be updated, either partially or entirely. This wording might be a trap in the exam question.
So what is the EXACT ANSWER? I think CDE... I saw this question when i study 1z0-071 test, but after i see these discusses, i cannot have confidence about my answer...
Be careful with the exam. I had this question but with a small detail:
one of answer was: very similar to C but "cannot" instead of "can"
C A table belonging to one user can have an index that belongs to a different use
(my exam) A table belonging to one user cannot have an index that belongs to a different use
So you must study a lot. To study this dump is not enought
A C E
A - True: To view all indexes, you can simply type: SELECT * FROM all_indexes;
B - False: "when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated." - https://docs.oracle.com/cd/B10501_01/server.920/a96521/indexes.htm
C - True: "To create an index in another schema, you must have CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either space quota on the tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege." - https://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/indexes.htm
D - False: All answers on Stack Overflow say the only way to do this is to first drop the unique index and recreate it as non-unique. - https://stackoverflow.com/questions/11979152/oracle-database-converting-unique-index-to-non-unique-one
E - True: (same as B) "when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated." - https://docs.oracle.com/cd/B10501_01/server.920/a96521/indexes.htm
F - False: "If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recycle bin. However, the indexes' names will not be the original names." - http://dba.fyicenter.com/faq/oracle/Recovered-Table-with-Index.html
AE - wrong that i agree
D - correct i agree too
F - if i update a non indexed column and the row does not change the position in the datafile (because the updated value uses the same amount of space) the index should not be touched right?
again here is the evidence that F correct and D is wrong
When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.
https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_in.htm
so final answers should be B, C , F
Thx
E - is not entirely true, you can’t recover it, but it still remains in the recycle bin and you have to drop it to recreate an index with the same name on the column again - so what means “permanently dropped”?
B - https://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/ch6_acce.htm
If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.
Using Bitmap Access Plans on Regular B*-tree Indexes
Note: This statement is executed by accessing indexes only, so no table access is necessary.
My tendency goes to BCD
I thing EF may also be possible, F more likely than E
I would also suggest: BCD
Especially for D Oracle says following:
The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes.
Source: https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895
But F seems also like it's true, just like you mentioned if someone updates only non indexed columns in the table, there shouldn't be any changes done to the index.
I am confused as to what the right answers are for sure - is it possible that out of the 6 choices 4 are right, but you only have to choose 3 of them?
E is wrong >
What Is the Recycle Bin?
The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
ama
Highly Voted 3 years, 10 months agoPhat
3 years, 5 months agoama
Highly Voted 3 years, 11 months agoMCzombie
3 years, 5 months agoit6567306
Most Recent 1 month, 3 weeks agozouve
10 months, 2 weeks agoauwia
11 months, 3 weeks ago[Removed]
9 months, 2 weeks agopiontk
1 year agoMarcello86CT
1 year, 12 months agoyarsalan
2 years, 5 months agoBorislone
2 years, 9 months agoSimoneF
3 years, 5 months agoit6567306
1 month, 3 weeks agogbab1792
3 years, 6 months agoMartinY
3 years, 8 months agorosiiieee
3 years, 9 months agodanito
3 years, 10 months agoNowOrNever
3 years, 9 months agoPhat
3 years, 5 months agoBananaSlug
3 years, 10 months agoBananaSlug
3 years, 10 months agoBananaSlug
3 years, 10 months agoBananaSlug
3 years, 10 months agoNowOrNever
3 years, 11 months agoama
3 years, 10 months agoama
3 years, 10 months agoNowOrNever
3 years, 10 months agoadoptc94
3 years, 10 months agoama
3 years, 11 months ago