Oracle Face Test

0. What are the advantages and disadvantages of binding variables and what are the use cases respectively?

Pros: The ability to avoid hard parsing of SQL and associated overhead (SQL syntax, semantic analysis, logical analysis, generating better execution plans, etc.) to improve execution efficiency.

Cons: Using bound variables on a table with data skew and uneven data distribution causes the optimizer to ignore its specific values, resulting in an inefficient execution plan and lower execution efficiency.

Use occasions:

OLTP (online transaction processing on-line transaction Processing) system: SQL statements are executed repeatedly in an OLTP system, but the amount of data processed is small and the result set is relatively small, especially with indexes on the table to narrow the intermediate result set. The parsing time is usually close to or higher than the execution time, so it is appropriate to use bound variables.

OLAP (online analytical processing on-line Analytical Processing) system: In OLAP systems, SQL statements are executed relatively infrequently, but the amount of data returned is large, so in most cases it tends to be more efficient to use the label scan. Its SQL statement executes much longer than its parsing time, so the use of bound variables has little effect on the total response time. and increase the risk of generating inefficient execution plans. That is, the performance of using literals in OLAP systems is higher than using bound variables.

1.Oracle database Use the (length ()) function "return string length", use the (UPPER ()) function "Capitalize all characters", use the (SUBSTR ()) function "intercept string", use the (To_char ()) function "Convert data type to character type ", use the (sysdate) function" Get current database Time ", use (SELECT * from v$version;) function" View database Version "

What is sequence in a 2.Oracle database? Please write the statement that establishes sequence.

Oracle provides a sequence object that provides self-growing serial numbers from the system, typically used to generate the self-growing primary key or ordinal of a database data record.

1 CREATESEQUENCE seqtest2INCREMENT by 1 --add a few each time3START with 1 --counting starting from 14Nomaxvalue--do not set the maximum value5Nocycle--keep accumulating, not looping6CACHETen;--sets the cache sequence, if the system is down or otherwise causes the sequence to be discontinuous or set to---------NOCACHE

How to get random records in a WF.TABLE_WF table in a 3.Oracle database, write a statement each time you fetch 20 entries.

SELECT * FROM (SELECT * from WF. TABLE_WF)

WHERE rownum<20;

How do I convert the string "20140715 235959" to a time format in a 4.Oracle database?

SELECT to_date (' 2014-07-15,23:59:59 ', ' Yyyy-mm-dd,hh24:mi:ss ') from DUAL;

Dsfdsafs

Oracle Face Test