Full Table Scan When a DBMS sees a query of the form like 1 2 3 SELECT * FROM R WHERE <condition> The obvious thing to do is read through the tuples of R and report these tuples that satisfy the condition. This is called a Full Table Scan. Selective Query If we have to report 80% of the tuples in R, it makes sense to do a full table scan....
Show Replication Factor of a File 1 hadoop fs -stat %r <YOUR_FILE_BLOCK> Update Replication Factor of a File 1 hadoop fs -setrep -R -w 3 <YOUR_FILE_BLOCK>
Try not to use SELECT * and provide specific fields Bad Example 1 SELECT * FROM user; Good Example 1 SELECT id, username, tel FROM user; Reason Specify fields will save computational resources, and reduce network expense. It’s possible using pre-built table index to reduce table returns, and improve query efficiency. Avoid using OR in the where clause to connect conditions Bad Example 1 2 SELECT id, username, tel FROM user WHERE id=1 OR salary=500; Good Example Use UNION ALL to concatenate results 1 2 3 4 5 SELECT id, username, tel FROM user WHERE id=1 UNION ALL SELECT id, username, tel FROM user WHERE salary=500; Use two separate SQL 1 2 SELECT id, username, tel FROM user WHERE id=1 1 2 SELECT id, username, tel FROM user WHERE salary=500; Reason The use of OR may invalidate the table index, and thus will do full table scan....
Query Execution Order Most people would write their SQL queries starting from SELECT part, because it’s more intuitive and close to our natural language. But actually that’s not the way that SQL queries been executed in query engine. Below is the execution order of a SQL query: FROM, JOIN. Tables are joined to get the base data. WHERE. The base data is filtered. GROUP BY. The filtered based data is grouped....
Join Full Join Inner Join Left Join Right Join Aggregation Function COUNT(). MAX(). MIN(). SUM(). AVG(). The GROUP BY statement groups rows that have the same values into summary rows, like ‘find the number of customers in each country’. The GROUP BY statement is often used with above aggregation functions to group the result-set by one or more columns.