x*****4
发帖数: 204 | 1 这里牛人多，最近面试一个software developer (SQL)的职位，被问了很多SQL的query
的技术问题，答得不是很满意.好吧，我承认水平太菜。牛人们帮忙看看到底怎么回答
，也为下一次的面试作准备。
1.How to optimize the large table if calling is very slow. Many joining
inside.
2.How many different types of index in the database
3.Do you have any experience on temporary table? What is the advantage to
use the temporary table?
4.In a stored table, without using cursor, how to go through the table to
check the column? |
k****r
发帖数: 807 | 2 不是牛人，瞎答哈，不对的地方还请真牛指教：
1.How to optimize the large table if calling is very slow. Many joining
inside.
1.1. use distributed system. The biggest two table can be partitioned
according to the join key to those nodes. Others can be replicated to each
node. first join the biggest two table.
1.2 Or, re-order the join operations to put the ones whose selectivity is
low. Then, the size of intermediate data is reduced.
1.3 Other optimization strategies can be used. Like to push selection as far
as possible. to push down projection..... I believe the interviewer is
trying to see how deep you understand the query optimizations.
2.How many different types of index in the database
As I know: hash index, B+ tree index, lookup table (not sure)
3.Do you have any experience on temporary table? What is the advantage to
use the temporary table?
Is that sub-result saved in memory? to improve the time latency.
4.In a stored table, without using cursor, how to go through the table to
check the column?
quesion about DBA? no idea..... |
n*******s
发帖数: 17267 | 3 这些都是SQL最基本的问题，回答这个有困难或许不适合去从事此类工作。
第一个问题估计是让你denormalize to improve performance, 其它几个你可以随便狗
一把，问index估计是想问你是否知道clustered 和non clustered index的区别吧，不
过我也很久没具体搞SQL了。 |
l******s
发帖数: 3045 | 4 1. if not touching infrastructure change, using temp tables to create small
tables with as less columns as possible for each joining element.
if touching infrastructure, you have the power to do db sharding you are
free to do a lot.
For a SQL question, I believe he's expecting the first answer.
2. non-clustered index and clustered index
3. Performance tunning, as I pointed out in #1
4. isn't it just a simple select statement? select column from table |
n*******s
发帖数: 17267 | 5 4问的是meta data 吧
small
are
【在 l******s 的大作中提到】
: 1. if not touching infrastructure change, using temp tables to create small
: tables with as less columns as possible for each joining element.
: if touching infrastructure, you have the power to do db sharding you are
: free to do a lot.
: For a SQL question, I believe he's expecting the first answer.
: 2. non-clustered index and clustered index
: 3. Performance tunning, as I pointed out in #1
: 4. isn't it just a simple select statement? select column from table
|
l******s
发帖数: 3045 | 6 用Cusor也查不到meta data啊
【在 n*******s 的大作中提到】
: 4问的是meta data 吧
:
: small
: are
|
n*******s
发帖数: 17267 | 7 我的意思是可以通过meta data 找到关于column的信息。
【在 l******s 的大作中提到】
: 用Cusor也查不到meta data啊
|
l******s
发帖数: 3045 | 8 是可以，但是对于这个问题里面的“without using cursor”，就没法理解了
【在 n*******s 的大作中提到】
: 我的意思是可以通过meta data 找到关于column的信息。
|
n*******s
发帖数: 17267 | 9 倒也是，没搞清楚具体问啥，单纯搞名字的话，desc, all_tab_columns都能弄出来，
mysql那种直接show columns也行，让楼主自己去狗吧。
【在 l******s 的大作中提到】
: 是可以，但是对于这个问题里面的“without using cursor”，就没法理解了
|
c*********3
发帖数: 1002 | 10 刚刚面试被问到了第一个问题…
query
【在 x*****4 的大作中提到】
: 这里牛人多，最近面试一个software developer (SQL)的职位，被问了很多SQL的query
: 的技术问题，答得不是很满意.好吧，我承认水平太菜。牛人们帮忙看看到底怎么回答
: ，也为下一次的面试作准备。
: 1.How to optimize the large table if calling is very slow. Many joining
: inside.
: 2.How many different types of index in the database
: 3.Do you have any experience on temporary table? What is the advantage to
: use the temporary table?
: 4.In a stored table, without using cursor, how to go through the table to
: check the column?
|
