SQL
SQL
Short notes:-
1.get Today Records: TO_CHAR(CREATE_STP, 'MM-DD-YYYY')=TO_CHAR(SYSDATE, 'MM-DD-YYYY')
2.Get UUDID: select sys_guid() from dual;
3.UUID to string : UPPER(REPLACE('5379efd7-cc4b-6fda-e053-2c97f4a10fdb','-'));
1.Second highest salary emp records
select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp))
2.Nth highest salary?
3.HAVING clause :-
https://www.techonthenet.com/oracle/having.php
SELECT exp1, exp2, ... expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expr1, exp2, ... expression_n HAVING having_condition;
Ex:- SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 25000;
4.Optimize queries based on the query optimization guidelines
Follow the SQL best practices to ensure query optimization:
1. Index all the predicates in JOIN, WHERE, ORDER BY and GROUP BY clauses.
WebSphere Commerce typically depends heavily on indexes to improve SQL performance and scalability. Without proper indexes, SQL queries can cause table scans, which causes either performance or locking problems.It is recommended that all predicate columns be indexed.
The exception being where column data has very low cardinality.
2. Avoid using functions in predicates.
The index is not used by the database if there is a function on the column. For example:
SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'
As a result of the function UPPER(), the index on COL1 is not used by database optimizers.
If the function cannot be avoided in the SQL, you need to create a function-based index in Oracle or generated columns in DB2 to improve performance.
3. Avoid using wildcard (%) at the beginning of a predicate.
The predicate LIKE '%abc' causes full table scan. For example:
SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'
This is a known performance limitation in all databases.
4. Avoid unnecessary columns in SELECT clause.
Specify the columns in the SELECT clause instead of using SELECT *.
The unnecessary columns places extra loads on the database, which slows down not just the single SQL, but the whole system.
5. Use inner join, instead of outer join if possible.
The outer join should only be used if it is necessary. Using outer join limits the database optimization options which typically results in slower SQL execution.
6.DISTINCT and UNION should be used only if it is necessary.
DISTINCT and UNION operators cause sorting, which slows down the SQL execution. Use UNION ALL instead of UNION, if possible, as it is much more efficient.
7.Oracle 10g and 11g requires that the CLOB/BLOB columns must be put at the end of the statements. Otherwise, it causes failure when the input value size is larger than 1000 characters.
8.The ORDER BY clause is mandatory in SQL if the sorted result set is expected.
The ORDER BY keyword is used to sort the result-set by specified columns. Without the ORDER BY clause, the result set is returned directly without any sorting. The order is not guaranteed. Be aware of the performance impact of adding the ORDER BY clause, as the database needs to sort the result set, resulting in one of the most expensive operations in SQL execution.
Short notes:-
1.get Today Records: TO_CHAR(CREATE_STP, 'MM-DD-YYYY')=TO_CHAR(SYSDATE, 'MM-DD-YYYY')
2.Get UUDID: select sys_guid() from dual;
3.UUID to string : UPPER(REPLACE('5379efd7-cc4b-6fda-e053-2c97f4a10fdb','-'));
1.Second highest salary emp records
select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp))
2.Nth highest salary?
3.HAVING clause :-
https://www.techonthenet.com/oracle/having.php
SELECT exp1, exp2, ... expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expr1, exp2, ... expression_n HAVING having_condition;
Ex:- SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 25000;
4.Optimize queries based on the query optimization guidelines
Follow the SQL best practices to ensure query optimization:
1. Index all the predicates in JOIN, WHERE, ORDER BY and GROUP BY clauses.
WebSphere Commerce typically depends heavily on indexes to improve SQL performance and scalability. Without proper indexes, SQL queries can cause table scans, which causes either performance or locking problems.It is recommended that all predicate columns be indexed.
The exception being where column data has very low cardinality.
2. Avoid using functions in predicates.
The index is not used by the database if there is a function on the column. For example:
SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'
As a result of the function UPPER(), the index on COL1 is not used by database optimizers.
If the function cannot be avoided in the SQL, you need to create a function-based index in Oracle or generated columns in DB2 to improve performance.
3. Avoid using wildcard (%) at the beginning of a predicate.
The predicate LIKE '%abc' causes full table scan. For example:
SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'
This is a known performance limitation in all databases.
4. Avoid unnecessary columns in SELECT clause.
Specify the columns in the SELECT clause instead of using SELECT *.
The unnecessary columns places extra loads on the database, which slows down not just the single SQL, but the whole system.
5. Use inner join, instead of outer join if possible.
The outer join should only be used if it is necessary. Using outer join limits the database optimization options which typically results in slower SQL execution.
6.DISTINCT and UNION should be used only if it is necessary.
DISTINCT and UNION operators cause sorting, which slows down the SQL execution. Use UNION ALL instead of UNION, if possible, as it is much more efficient.
7.Oracle 10g and 11g requires that the CLOB/BLOB columns must be put at the end of the statements. Otherwise, it causes failure when the input value size is larger than 1000 characters.
8.The ORDER BY clause is mandatory in SQL if the sorted result set is expected.
The ORDER BY keyword is used to sort the result-set by specified columns. Without the ORDER BY clause, the result set is returned directly without any sorting. The order is not guaranteed. Be aware of the performance impact of adding the ORDER BY clause, as the database needs to sort the result set, resulting in one of the most expensive operations in SQL execution.
Comments
Post a Comment