How you will avoid your query from using indexes?

How you will avoid your query from using indexes?
by

1 Answer

Answer :

SELECT * FROM emp
Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes
in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp
where emp_no=1234;
i.e using HINTS
by

Related questions

How you will avoid duplicating records in a query?

Description : How you will avoid duplicating records in a query?

Last Answer : By using DISTINCT

What is the use of INDEXES option in IMP command ?

Description : What is the use of INDEXES option in IMP command ?

Last Answer : A flag to indicate whether import should import index on tables or not.

What is the use of INDEXES option in EXP command ?

Description : What is the use of INDEXES option in EXP command ?

Last Answer : A flag to indicate whether indexes on tables will be exported.

Can you built indexes online?

Description : Can you built indexes online?

Last Answer : YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the ... online index build operations: CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

When should you rebuilt indexes?

Description : When should you rebuilt indexes?

Last Answer : In 90% cases - NEVER. When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based. Also index blevel is one of the key indicators of performance of sql queries doing Index range scans.

A table is having few rows, should you create indexes on this table

Description : A table is having few rows, should you create indexes on this table

Last Answer : Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large. You can create an index on any column; however, if the ... , creating an index on the column does not increase performance and the index takes up resources unnecessarily.

What type of indexes are available in Oracle?

Description : What type of indexes are available in Oracle?

Last Answer : There are many index types within Oracle: B*Tree Indexes - common indexes in Oracle. They are similar construct to a binary tree; they provide fast access by key, to an individual row or range of ... They allow data to be sorted from big to small (descending) instead of small to big (ascending).

On which columns you should create Indexes?

Description : On which columns you should create Indexes?

Last Answer : In general, you should create an index on a column in any of the following situations: ▪ The column is queried frequently ▪ A referential integrity constraint exists on the column ▪ A UNIQUE key ... ORDER BY clauses ▪ On columns that have few of the same values or unique values in the table

If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?

Description : If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?

Last Answer : Only for 10 records.

What is a Query Record Group?

Description : What is a Query Record Group?

Last Answer : A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default names, data types, had lengths from the database columns ... rows retrieved by the query associated with that record group. What is a Non Query Record Group?

What is the Difference between a post query and a pre query

Description : What is the Difference between a post query and a pre query

Last Answer : A post query will fire for every row that is fetched but the pre query will fire only once.

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Description : Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Last Answer : SELECT customer_no, payments from customer C1 WHERE 3

What is correlated sub-query ?

Description : What is correlated sub-query ?

Last Answer : Correlated sub query is a sub query which has reference to the main query.

There is a % sign in one field of a column. What will be the query to find it?

Description : There is a % sign in one field of a column. What will be the query to find it?

Last Answer : '' Should be used before '%'.

My query was fine last week and now it is slow. Why? (for DBA

Description : My query was fine last week and now it is slow. Why? (for DBA

Last Answer : The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was ... the plan should be? Run the query with hints to see if this produces the required performance.

A query fetched 10 records How many times does a PRE- QUERY Trigger and POST-QUERY Trigger will get executed ?

Description : A query fetched 10 records How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?

Last Answer : PRE-QUERY fires once. POST-QUERY fires 10 times.

When is PRE-QUERY trigger executed ?

Description : When is PRE-QUERY trigger executed ?

Last Answer : When Execute-query or count-query Package procedures are invoked.

Why query fails sometimes ?

Description : Why query fails sometimes ?

Last Answer : Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments.

Where is the external query executed at the client or the server?

Description : Where is the external query executed at the client or the server?

Last Answer : At the server.

How is it possible to select generate a select set for the query in the query property sheet?

Description : How is it possible to select generate a select set for the query in the query property sheet?

Last Answer : By using the tables/columns button and then specifying the table and the column names.

What is a difference between pre-select and pre-query?

Description : What is a difference between pre-select and pre-query?

Last Answer : Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires ... the query criteria in enter query mode.Pre-query trigger fires before pre-select trigger.

Is it possible to modify an external query in a report which contains it?

Description : Is it possible to modify an external query in a report which contains it?

Last Answer : No.

If a parameter is used in a query without being previously defined, what diff. exist betw. report 2.0 and 2.5 when the query is applied?

Description : If a parameter is used in a query without being previously defined, what diff. exist betw. report 2.0 and 2.5 when the query is applied?

Last Answer : While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.

What is a query?

Description : What is a query?

Last Answer : A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.

What is meant by query optimization?

Description : What is meant by query optimization?

Last Answer : The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

What is Query evaluation engine?

Description : What is Query evaluation engine?

Last Answer : It executes low-level instruction generated by compiler.

What is ORA-01555 - Snapshot Too Old error and how do you avoid it?

Description : What is ORA-01555 - Snapshot Too Old error and how do you avoid it?

Last Answer : The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 11:30 AM, Oracle ensures that all rows are as they appeared at 11:30 AM, ... -01555 snapshot too old also relates to your setting for automatic undo retention ▪ Don't fetch between commits

What is ORA-01555 - Snapshot Too Old error and how do you avoid it?

Description : What is ORA-01555 - Snapshot Too Old error and how do you avoid it?

Last Answer : The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 11:30 AM, Oracle ensures that all rows are as they appeared at 11:30 AM, ... -01555 snapshot too old also relates to your setting for automatic undo retention ▪ Don't fetch between commits

How are book indexes made?

Description : How are book indexes made?

Last Answer : answer:There are many options for generating an index. Paid software like Cindex for ~$500, services that do it for you for $100-200, or free software. You can download this free indexer PDF Index ... need to read through and make sure it gets what you want and reports only the important key words.

Which grouping do you work with when formatting text in word? 1) Tables, paragraphs and indexes 2) Paragraphs, indexes and sections 3) Characters, sections and paragraphs 4) Indexes, characters and tables 5) None of these

Description : Which grouping do you work with when formatting text in word? 1) Tables, paragraphs and indexes 2) Paragraphs, indexes and sections 3) Characters, sections and paragraphs 4) Indexes, characters and tables 5) None of these

Last Answer : Answer :1

You organize files by storing them in a) Archives b) Folders c) Indexes d) Lists

Description : You organize files by storing them in a) Archives b) Folders c) Indexes d) Lists

Last Answer : b) Folders

You organise files by storing them in a) Archives b) Folders c) Indexes d) Lists e) None of these

Description : You organise files by storing them in a) Archives b) Folders c) Indexes d) Lists e) None of these

Last Answer : b) Folders

The Journal first published in 1889, featured the Jones 'Average', the first of several indexes of stock and bond prices on the New York Stock Exchange. How do we know that today?

Description : The Journal first published in 1889, featured the Jones 'Average', the first of several indexes of stock and bond prices on the New York Stock Exchange. How do we know that today?

Last Answer : Answer: Wall Street Journal

Data which improves the performance and accessibility of the database are called: (A) Indexes (B) User Data (C) Application Metadata (D) Data Dictionary

Description : Data which improves the performance and accessibility of the database are called: (A) Indexes (B) User Data (C) Application Metadata (D) Data Dictionary

Last Answer : (A) Indexes

Consider the following two statements : (a) Data scrubling is a process to upgrade the quality of data, before it is moved into Data warehouse. (b) Data scrubling is a process of rejecting data from data warehouse to create indexes. Which one of the following options is correct ? (A) (a) is true, (b) is false (B) (a) is false, (b) is true (C) Both (a) and (b) are false (D) Both (a) and (b) are true

Description : Consider the following two statements : (a) Data scrubling is a process to upgrade the quality of data, before it is moved into Data warehouse. (b) Data scrubling is a process of rejecting data from data warehouse to create ... true (C) Both (a) and (b) are false (D) Both (a) and (b) are true

Last Answer : (A) (a) is true, (b) is false

Searching using query on Internet is, use of ___________ type of agent. a) Offline agent b) Online agent c) Both Offline & Online agent d) Goal Based & Online agent

Description : Searching using query on Internet is, use of ___________ type of agent. a) Offline agent b) Online agent c) Both Offline & Online agent d) Goal Based & Online agent

Last Answer : d) Goal Based & Online agent

This operates algorithmically or using a mixture of algorithmic and human input to collect, index, store and retrieve information on the web (e.g. web pages, images, information and other types of files). It makes the information available to users in a manageable and meaningful way in response to a search query. This is referred to as: 1. Banner ads 2. Pop-up ads 3. A search engine 4. Apps

Description : This operates algorithmically or using a mixture of algorithmic and human input to collect, index, store and retrieve information on the web (e.g. web pages, images, information and other types of files). It makes the ... is referred to as: 1. Banner ads 2. Pop-up ads 3. A search engine 4. Apps

Last Answer : A search engine

What is JDBC? Describe the steps needed to execute a SQL query using JDBC.

Description : What is JDBC? Describe the steps needed to execute a SQL query using JDBC.

Last Answer : Ans. The JDBC is a pure Java API used to execute SQL statements. It provides a set of classes and interfaces that can be used by developers to write database applications. The steps needed to execute a ... . 2. Execute a SQL statement. 3. Process th results. 4. Close the connection to the database.

Consider the following schemas: Branch = (Branch-name, Assets, Branch-city) Customer = (Customer-name, Bank-name, Customer-city) Borrow = (Branch-name, loan-number, customer account-number) Deposit = (Branch-name, Account-number, Customer-name, Balance) Using relational Algebra, the query that finds customers who have balance more than 10,000 is ................. (A) πcustomer-name(σbalance> 10000(Deposit)) (B) σcustomer-name(σbalance> 10000(Deposit)) (C) πcustomer-name(σbalance> 10000(Borrow)) (D) σcustomer-name(σbalance> 10000(Borrow))

Description : Consider the following schemas: Branch = (Branch-name, Assets, Branch-city) Customer = (Customer-name, Bank-name, Customer-city) Borrow = (Branch-name, loan-number, customer account-number) Deposit = ... )) (C) πcustomer-name(σbalance> 10000(Borrow)) (D) σcustomer-name(σbalance> 10000(Borrow))

Last Answer : (A) πcustomer-name(σbalance> 10000(Deposit)) 

How does one backup a database using the export utility? (for DBA

Description : How does one backup a database using the export utility? (for DBA

Last Answer : Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data ... exports include more information about the database in the export file than user level exports.

Give the reasoning behind using an index.

Description : Give the reasoning behind using an index.

Last Answer : Faster access to data blocks in a table.

What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

Description : What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

Last Answer : 1,000,00

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

Description : What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

Last Answer : % TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in ... database definition of a column in a table changes, the data type of a variable changes accordingly.

Why is Oracle not using the damn index? (for DBA

Description : Why is Oracle not using the damn index? (for DBA

Last Answer : This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster ... with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

How does one backup a database using RMAN? (for DBA

Description : How does one backup a database using RMAN? (for DBA

Last Answer : The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database ... directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.

How will you enforce security using stored procedures?

Description : How will you enforce security using stored procedures?

Last Answer : Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

How can one see who is using a temporary segment? (for DBA

Description : How can one see who is using a temporary segment? (for DBA

Last Answer : For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'. All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is ... For usage stats, see SYS.V_$SORT_SEGMENT From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these

How will you enforce security using stored procedures?

Description : How will you enforce security using stored procedures?

Last Answer : Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

What is Enterprise Structure Configurator (ESC) and what are the benefits of using it?

Description : What is Enterprise Structure Configurator (ESC) and what are the benefits of using it?

Last Answer : The ESC is an interview-based tool that guides you through the configuration of your enterprise structures. Use the ESC as part of your set up to define the organization structures and job and position structures of the enterprise.

What are stored-procedures? And what are the advantages of using them.

Description : What are stored-procedures? And what are the advantages of using them.

Last Answer : Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.